CREATE VIEW [dbo].[vStoreFeaturedProducts] AS
    
    SELECT op.[OrderProductID],
           op.[ProductCode],
           op.[Title],
           CONVERT(varchar(max),op.[Description]) [Description],
           op.[IsSuperProduct],
           op.[SellOnWeb]
      FROM [dbo].[OrderProductCategoryLookup] opcl
           INNER JOIN [dbo].[OrderProduct] op ON opcl.[OrderProductID] = op.[OrderProductID]
     WHERE opcl.[IsFeatured] = 1
           AND op.[SellOnWeb] != 0
           AND op.[IsSuperProduct] = 1

UNION       

    SELECT op.[OrderProductID],
           op.[ProductCode],
           p.[TITLE] [Title],
           CONVERT(varchar(max),
               CASE
                   WHEN (p.[WEB_DESC] LIKE '' OR p.[WEB_DESC] IS NULL) THEN p.[DESCRIPTION]
                   ELSE p.[WEB_DESC]
               END) [Description],
           op.[IsSuperProduct],
           p.[WEB_OPTION] [SellOnWeb]
      FROM [dbo].[OrderProductCategoryLookup] opcl
           INNER JOIN [dbo].[OrderProduct] op ON opcl.[OrderProductID] = op.[OrderProductID]
           INNER JOIN [dbo].[Product] p ON p.[PRODUCT_CODE] = op.[ProductCode]
     WHERE opcl.[IsFeatured] = 1
           AND p.[WEB_OPTION] != 0
           AND op.[IsSuperProduct] = 0

GO